﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using CommonLibrary.DataModel.Basic;
using CommonLibrary.DB;
using System.Data;
using CommonLibrary.Common;

namespace ServiceApplication.Dao.Basic
{
    public class MsgDao
    {
        /// <summary>
        /// 
        /// </summary>
        /// <param name="msgEntity"></param>
        /// <returns>刚插入的主键</returns>
        public int AddEntity(MsgEntity msgEntity)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@msg_id", msgEntity.msg_id));
            parameterList.Add(new SqlParameter("@msg_titile", msgEntity.msg_titile));
            parameterList.Add(new SqlParameter("@msg_content", msgEntity.msg_content));
            parameterList.Add(new SqlParameter("@msg_content2", msgEntity.msg_content2));
            parameterList.Add(new SqlParameter("@msg_sendtime", msgEntity.msg_sendtime));
            parameterList.Add(new SqlParameter("@creater_id", msgEntity.creater_id));
            parameterList.Add(new SqlParameter("@receiver_ids", msgEntity.receiver_ids));
            parameterList.Add(new SqlParameter("@msg_isread", msgEntity.msg_isread));
            parameterList.Add(new SqlParameter("@msg_lev", msgEntity.msg_lev));
            parameterList.Add(new SqlParameter("@msg_appendix", msgEntity.msg_appendix));
            parameterList.Add(new SqlParameter("@msg_save", msgEntity.msg_save));
            parameterList.Add(new SqlParameter("@msg_appendix_name", msgEntity.msg_appendix_name));


            string sql = "insert into jc_msg (msg_titile,msg_content,msg_content2,msg_sendtime,creater_id,receiver_ids,msg_isread,msg_lev,msg_appendix,msg_save,msg_appendix_name) values " +
                " (@msg_titile,@msg_content,@msg_content2,@msg_sendtime,@creater_id,@receiver_ids,@msg_isread,@msg_lev,@msg_appendix,@msg_save,@msg_appendix_name); " +
                " select @@identity";
            string id = DBExecuteUtil.getUniqueResult(sql, parameterList.ToArray());
            return int.Parse(id);
        }

        public Int32 AddChatInfo(ChatInfoEntity chatInfoEntity)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@chat_user", chatInfoEntity.chat_user));
            parameterList.Add(new SqlParameter("@chat_peer", chatInfoEntity.chat_peer));
            parameterList.Add(new SqlParameter("@init_time", chatInfoEntity.init_time));
            parameterList.Add(new SqlParameter("@checked", chatInfoEntity.check));
            parameterList.Add(new SqlParameter("@check_time", chatInfoEntity.check_time));

            String sql = "INSERT INTO chat_info ([chat_id],[chat_user],[chat_peer],[init_time],[cheched],[check_time]) values" +
                "(@chat_user,@chat_peer,@init_time,@checked,@check_time)";

            String id = DBExecuteUtil.getUniqueResult(sql, parameterList.ToArray());

            return Int32.Parse(id);
        }

        public void UpdateChatInfo(ChatInfoEntity chatInfoEntity)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@chat_id", chatInfoEntity.chat_id));
            parameterList.Add(new SqlParameter("@checked", chatInfoEntity.check));
            parameterList.Add(new SqlParameter("@chech_time", chatInfoEntity.check_time));
            String sql = "UPDATE chat_info SET [checked]=@checked, [checked_time]=@check_time WHERE [chat_id]=@chat_id";

            DBExecuteUtil.executeSqlWithPar(sql, parameterList.ToArray());
        }

        public void QueryEntitys(String userName)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@chat_peer", userName));
            String sql = "SELECT [chat_id],[chat_user],[chat_peer],[init_time],[checked],[check_time] WHERE [chat_peer]=@chat_peer";
            DBExecuteUtil.executeSqlWithPar(sql, parameterList.ToArray());
        }

        public void DleteEntity(int msg_id)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@msg_id", msg_id));
            string sql = "delete from jc_msg where msg_id=@msg_id ";
            DBExecuteUtil.executeSqlWithPar(sql, parameterList.ToArray());
        }

        public void UpdateEntity(MsgEntity msgEntity)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@msg_id", msgEntity.msg_id));
            parameterList.Add(new SqlParameter("@msg_titile", msgEntity.msg_titile));
            parameterList.Add(new SqlParameter("@msg_content", msgEntity.msg_content));
            parameterList.Add(new SqlParameter("@msg_content2", msgEntity.msg_content2));
            parameterList.Add(new SqlParameter("@msg_sendtime", msgEntity.msg_sendtime));
            parameterList.Add(new SqlParameter("@creater_id", msgEntity.creater_id));
            parameterList.Add(new SqlParameter("@receiver_ids", msgEntity.receiver_ids));
            parameterList.Add(new SqlParameter("@msg_isread", msgEntity.msg_isread));
            parameterList.Add(new SqlParameter("@msg_lev", msgEntity.msg_lev));
            parameterList.Add(new SqlParameter("@msg_appendix", msgEntity.msg_appendix));
            parameterList.Add(new SqlParameter("@msg_save", msgEntity.msg_save));
            parameterList.Add(new SqlParameter("@msg_appendix_name", msgEntity.msg_appendix_name));

            string sql = "update jc_msg set msg_titile=@msg_titile,msg_content=@msg_content,msg_content2=@msg_content2,msg_sendtime=@msg_sendtime, " +
                             " creater_id=@creater_id,receiver_ids=@receiver_ids,msg_isread=@msg_isread,msg_lev=@msg_lev," +
                             "msg_appendix=@msg_appendix, msg_save=@msg_save,msg_appendix_name=@msg_appendix_name where msg_id=@msg_id";

            DBExecuteUtil.executeSqlWithPar(sql, parameterList.ToArray());
        }

        public MsgEntity QueryEntity(int msg_id)
        {
            MsgEntity msgEntity = null;
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@msg_id", msg_id));
            string sql = "select msg_id, msg_titile,msg_content,msg_content2,msg_sendtime,creater_id,receiver_ids,msg_isread,msg_lev,msg_appendix,msg_appendix_name from jc_msg where msg_id=@msg_id";

            var result = DBExecuteUtil.querySqlArray(sql, parameterList.ToArray());
            if (result.Length == 0) return null;

            msgEntity = new MsgEntity();
            msgEntity.msg_id = int.Parse(result[0][0]);
            msgEntity.msg_titile = result[0][1];
            msgEntity.msg_content = result[0][2];
            msgEntity.msg_content2 = result[0][3];
            msgEntity.msg_sendtime = result[0][4];
            msgEntity.creater_id = int.Parse(result[0][5]);
            msgEntity.receiver_ids = result[0][6];
            msgEntity.msg_isread = result[0][7];
            msgEntity.msg_lev = int.Parse(result[0][8]);
            msgEntity.msg_appendix = result[0][9];
            msgEntity.msg_appendix_name = result[0][10];
            return msgEntity;
        }

        public DataTable QuerySavedEntity(String userId)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@userId", userId));
            string sql = "select msg_id as 编号, msg_titile as 标题, msg_sendtime as 保存时间 from jc_msg where creater_id=@userid and msg_save = 1";
            var result = DBExecuteUtil.querySqlTable(sql, parameterList.ToArray());
            return result;
        }

        public DataTable QuerySavedEntityByCondition(String userId, String title, String content, String stime, String etime)
        {

            string sql = "select msg_id as 编号, msg_titile as 标题, msg_sendtime as 保存时间 from jc_msg where creater_id=@userid and msg_save = 1";
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@userId", userId));
            if (!String.IsNullOrEmpty(title))
            {
                sql += " and msg_titile like '%' + @title + '%'";
                parameterList.Add(new SqlParameter("@title", title));
            }
            if (!String.IsNullOrEmpty(content))
            {
                sql += " and msg_content like '%' + @content + '%'";
                parameterList.Add(new SqlParameter("@content", content));
            }
            if (!String.IsNullOrEmpty(stime))
            {
                sql += " and convert(datetime,msg_sendtime) >= convert(datetime,@stime)";
                parameterList.Add(new SqlParameter("@stime", stime));
            }
            else if (!String.IsNullOrEmpty(etime))
            {
                sql += " and convert(datetime,msg_sendtime) >= convert(datetime,@stime)";
                parameterList.Add(new SqlParameter("@stime", DateTime.Now.ToString("yyyy-MM-dd") + " 00:00:00"));
            }

            if (!String.IsNullOrEmpty(etime))
            {
                sql += " and convert(datetime,msg_sendtime) <= convert(datetime,@etime)";
                parameterList.Add(new SqlParameter("@etime", etime));
            }
            else if (!String.IsNullOrEmpty(stime))
            {
                sql += " and convert(datetime,msg_sendtime) <= convert(datetime,@etime)";
                parameterList.Add(new SqlParameter("@etime", DateTime.Now.ToString("yyyy-MM-dd") + " 23:59:59"));
            }
            sql += " order by msg_sendtime desc ";

            var dt = DBExecuteUtil.querySqlTable(sql, parameterList.ToArray());
            return dt;
        }

        public DataTable QueryAll()
        {
            string sql = " select msg_id 编码, msg_titile 标题,msg_content 内容,msg_content2 内容2,msg_sendtime 发送时间,creater_id 发起人编码,u.user_name 发起人,receiver_ids 接收人编码组,msg_isread 已读,msg_lev 等级,msg_appendix as 附件,msg_save as 保存 " +
                             " from jc_msg m,jc_user u where m.creater_id=u.user_id  order by m.msg_sendtime desc ";
            DataTable dt = DBExecuteUtil.querySqlTable(sql);
            return dt;
        }

        public DataTable QueryMsgByEntity(MsgEntity msgEntity, String stime, String etime)
        {
            string sql = " select msg_id 编码, msg_titile 标题,msg_content 内容,msg_content2 内容2,msg_sendtime 发送时间,creater_id 发起人编码,u.user_name 发起人," +
                "receiver_ids 接收人编码组,msg_isread 已读,msg_lev 等级,msg_appendix as 附件,msg_save as 保存 " +
                "from jc_msg m,jc_user u where m.creater_id=u.user_id";
            List<SqlParameter> parameterList = new List<SqlParameter>();
            if (msgEntity.creater_id != 0)
            {
                sql += " and creater_id=@createId ";
                parameterList.Add(new SqlParameter("@createId", msgEntity.creater_id));
            }
            if (!String.IsNullOrEmpty(msgEntity.msg_content))
            {
                sql += " and msg_content like '%' +@content + '%'";
                parameterList.Add(new SqlParameter("@content", msgEntity.msg_content));
            }
            if (!String.IsNullOrEmpty(stime))
            {
                stime = DateUtil.getBeginTimeBySecond(DateTime.Parse(stime));
                sql += " and convert(datetime,msg_sendtime) >= convert(datetime,@stime)";
                parameterList.Add(new SqlParameter("@stime", stime));
            }
            if (!String.IsNullOrEmpty(etime))
            {
                etime = DateUtil.getEndTimeBySecond(DateTime.Parse(etime));
                sql += " and convert(datetime,msg_sendtime) <= convert(datetime,@etime)";
                parameterList.Add(new SqlParameter("@etime", etime));
            }
            if (!String.IsNullOrEmpty(msgEntity.msg_isread))
            {
                sql += " and msg_isread=@msg_isread ";
                parameterList.Add(new SqlParameter("@msg_isread", msgEntity.msg_isread));
            }
            if (!String.IsNullOrEmpty(msgEntity.msg_titile))
            {
                sql += " and msg_titile like '%" + msgEntity.msg_titile + "%'";
            }
            //保存标志 默认是0
            sql += " and msg_save ='" + msgEntity.msg_save + "'";

            sql += " order by m.msg_sendtime desc ";

            var dt = DBExecuteUtil.querySqlTable(sql, parameterList.ToArray());
            return dt;
        }


        public DataTable QueryRecMsgByEntity(MsgEntity msgEntity, int receiver_id, String stime, String etime)
        {
            string sql = " select m.msg_id 编码, msg_titile 标题,msg_content 内容,msg_content2 内容2,msg_sendtime 发送时间,creater_id 发起人编码,u.user_name 发起人," +
                "receiver_ids 接收人编码组,msgmx_isread 已读,msg_lev 等级,msg_appendix as 附件,msg_save as 保存 " +
                "from jc_msg m,jc_user u,jc_msgmx mx where m.creater_id=u.user_id and m.msg_id=mx.msg_id ";
            List<SqlParameter> parameterList = new List<SqlParameter>();
            if (msgEntity.creater_id != 0)
            {
                sql += " and creater_id=@createId ";
                parameterList.Add(new SqlParameter("@createId", msgEntity.creater_id));
            }
            if (!String.IsNullOrEmpty(msgEntity.msg_content))
            {
                sql += " and msg_content like '%' +@content + '%'";
                parameterList.Add(new SqlParameter("@content", msgEntity.msg_content));
            }
            if (!String.IsNullOrEmpty(stime))
            {
                stime = DateUtil.getBeginTimeBySecond(DateTime.Parse(stime));
                sql += " and convert(datetime,msg_sendtime) >= convert(datetime,@stime)";
                parameterList.Add(new SqlParameter("@stime", stime));
            }
            if (!String.IsNullOrEmpty(etime))
            {
                etime = DateUtil.getEndTimeBySecond(DateTime.Parse(etime));
                sql += " and convert(datetime,msg_sendtime) <= convert(datetime,@etime)";
                parameterList.Add(new SqlParameter("@etime", etime));
            }
            if (!String.IsNullOrEmpty(msgEntity.msg_titile))
            {
                sql += " and msg_titile like '%" + msgEntity.msg_titile + "%'";
            }

            //receive_id
            sql += " and mx.receiver_id =@receiver_id";
            parameterList.Add(new SqlParameter("@receiver_id", receiver_id));

            //保存标志 默认是0
            sql += " and msg_save ='" + msgEntity.msg_save + "'";

            sql += " order by m.msg_sendtime desc ";

            var dt = DBExecuteUtil.querySqlTable(sql, parameterList.ToArray());
            return dt;
        }

        public DataTable QueryMsg(int creater_id, int receiver_id, string title, string content, string stime, string etime)
        {
            string sql = " select m.msg_id 编码, msg_titile 标题,msg_content 内容,msg_content2 内容2,msg_sendtime 发送时间,creater_id 发起人编码,u.user_name 发起人," +
                "receiver_ids 接收人编码组,msgmx_isread 已读,msg_lev 等级,msg_appendix as 附件,msg_save as 保存 " +
                "from jc_msg m,jc_user u,jc_msgmx mx where m.creater_id=u.user_id and m.msg_id=mx.msg_id ";
            List<SqlParameter> parameterList = new List<SqlParameter>();
            if (creater_id != 0)
            {
                sql += " and creater_id=@createId ";
                parameterList.Add(new SqlParameter("@createId", creater_id));
            }
            if (receiver_id != 0)
            {
                sql += " and mx.receiver_id =@receiver_id ";
                parameterList.Add(new SqlParameter("@receiver_id", receiver_id));
            }
            if (!String.IsNullOrEmpty(content))
            {
                sql += " and msg_content like '%' +@content + '%'";
                parameterList.Add(new SqlParameter("@content", content));
            }
            if (!String.IsNullOrEmpty(stime))
            {
                stime = DateUtil.getBeginTimeBySecond(DateTime.Parse(stime));
                sql += " and convert(datetime,msg_sendtime) >= convert(datetime,@stime)";
                parameterList.Add(new SqlParameter("@stime", stime));
            }
            if (!String.IsNullOrEmpty(etime))
            {
                etime = DateUtil.getEndTimeBySecond(DateTime.Parse(etime));
                sql += " and convert(datetime,msg_sendtime) <= convert(datetime,@etime)";
                parameterList.Add(new SqlParameter("@etime", etime));
            }
            if (!String.IsNullOrEmpty(title))
            {
                sql += " and msg_titile like '%" + title + "%'";
            }

            sql += " order by m.msg_sendtime desc ";

            var dt = DBExecuteUtil.querySqlTable(sql, parameterList.ToArray());
            return dt;
        }

        public void AddEmail(EmailEntity emailEnty)
        {

            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@email_title", emailEnty.Email_Title));
            parameterList.Add(new SqlParameter("@email_context", emailEnty.Email_Context));
            parameterList.Add(new SqlParameter("@email_address", emailEnty.Email_Address));
            parameterList.Add(new SqlParameter("@email_receives", emailEnty.Email_Receives));
            parameterList.Add(new SqlParameter("@user_id", emailEnty.User_Id));
            parameterList.Add(new SqlParameter("@appendex_name", emailEnty.Appendix_Name));
            parameterList.Add(new SqlParameter("@appendex_id", emailEnty.Appendix_Id));
            String sql = "INSERT INTO Email (user_id,email_title,email_context,email_address,email_receives,appendix_name,appendix_id) values " +
                "(@user_id,@email_title,@email_context,@email_address,@email_receives,@appendex_name,@appendex_id)";

            DBExecuteUtil.executeSqlWithPar(sql, parameterList.ToArray());
        }

        public DataTable QueryEmails(String userId)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@user_id", userId));
            String sql = "SELECT email_title as 标题, email_id as 编号 FROM Email WHERE user_id=@user_id AND sended=0";
            var result = DBExecuteUtil.querySqlTable(sql, parameterList.ToArray());
            return result;
        }

        public EmailEntity QueryEmail(String emailId)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@email_id", emailId));
            String sql = "SELECT user_id,email_title,email_context,email_address,email_receives,appendix_name,appendix_id FROM Email WHERE email_id=@email_id";
            var result = DBExecuteUtil.querySqlTable(sql, parameterList.ToArray());

            var emailEnty = new EmailEntity();
            emailEnty.User_Id = result.Rows[0]["user_id"].ToString();
            emailEnty.Email_Address = result.Rows[0]["email_address"].ToString();
            emailEnty.Email_Context = result.Rows[0]["email_context"].ToString();
            emailEnty.Email_Id = emailId;
            emailEnty.Email_Receives = result.Rows[0]["email_receives"].ToString();
            emailEnty.Email_Title = result.Rows[0]["email_title"].ToString();
            emailEnty.Appendix_Name = result.Rows[0]["appendix_name"].ToString();
            emailEnty.Appendix_Id = result.Rows[0]["appendix_id"].ToString();

            return emailEnty;
        }

        public void UpdateMail(EmailEntity emailEnty)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@emailId", emailEnty.Email_Id));
            parameterList.Add(new SqlParameter("@email_title", emailEnty.Email_Title));
            parameterList.Add(new SqlParameter("@email_context", emailEnty.Email_Context));
            parameterList.Add(new SqlParameter("@email_address", emailEnty.Email_Address));
            parameterList.Add(new SqlParameter("@email_receives", emailEnty.Email_Receives));
            parameterList.Add(new SqlParameter("@appendex_name", emailEnty.Appendix_Name));
            parameterList.Add(new SqlParameter("@appendex_id", emailEnty.Appendix_Id));
            String sql = "UPDATE Email SET email_title=@email_title,email_context=@email_context,email_address=@email_address,email_receives=@email_receives," +
                "appendix_name=@appendex_name,appendix_id=@appendex_id WHERE email_id=@emailId";

            DBExecuteUtil.executeSqlWithPar(sql, parameterList.ToArray());
        }

        public void SendedEmail(String emailId)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@emailId", emailId));
            String sql = "UPDATE Email SET sended=1 WHERE email_id=@emailId";

            DBExecuteUtil.executeSqlWithPar(sql, parameterList.ToArray());
        }

    }
}
